import openpyxl
print('正在使用加载数据模块...')

client_list = []

#下面是client的结构
# client = {
#     'name':'TBD',
#     'date':'TBD',
#     'total':'TBD',
#     'summary':'TBD',
#     'nav':[],  #里面是一个元组('2020-7-31', 98765.4, 385.8)
#     'holdings':[],
#     'alloc':[],
#     'comment':'TBD'
# }

def load_data(filename):
    excel = openpyxl.load_workbook(filename)
    for ws in excel:
        client = {}
        client['name'] = client_name(ws)
        client['date'] = report_date(ws)
        client['navs'] = client_navs(ws)
        client['holdings'] = client_holdings(ws)
        client['alloc'] = client_alloc(ws)
        client_list.append(client)

def client_name(ws):
    return ws['B1'].value

def report_date(ws):
    return ws['B2'].value

def client_navs(ws):
    navs = []
    for row in ws.iter_rows(min_row=7, max_row=14):
       date = row[0].value
       value = row[1].value
       change = row[2].value
    #    if change == None or change.strip == '':
    #        change = value - navs[-1][1]
       if len(navs) > 0:
           change = value - navs[-1][1]
       navs.append((date, value, change))
    return navs

def client_holdings(ws):
    holdings = []
    start_row = 0
    for index, row in enumerate(ws.rows):
        if row[0].value == '十大重仓股':
            start_row = index + 3
            break
    print(f'holdings的开始行是第{start_row}行')
    for row in ws.iter_rows(min_row = start_row):
        code = row[0].value
        if code == None or code.strip == '':
            break
        holdings.append((code, row[1].value, row[2].value))
    
    return holdings

def client_alloc(ws):
    start_index = 0
    for index, row in enumerate(ws.rows):
        if row[0].value == '投资组合':
            start_index = index + 2
            break 
    alloc = []
    for row in ws.iter_rows(min_row=start_index):
        if row[0].value == None or row[0].value.strip == '':
            break
        alloc.append((row[0].value, row[1].value))
    return alloc

    

load_data('data1.xlsx')
print(client_list)
